﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class WTManager:ConnectionManager
    {
        #region Adding/Updating
        public SqlException AddTransfer(WTUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OWT (DocNum, PostingDate, Reference, SourceWhCode, DestWhCode, Remarks, Driver, Helper, DocStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                            "(@DocNum, @PostingDate,  @Reference, @SourceWhCode, @DestWhCode, @Remarks, @Driver, @Helper, @DocStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy) ";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@SourceWhCode", oUnit.SourceWarehouse));
                oCommand.Parameters.Add(new SqlParameter("@DestWhCode", oUnit.DestinationWarehouse));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Driver", oUnit.Driver));
                oCommand.Parameters.Add(new SqlParameter("@Helper", oUnit.Helper));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();


                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    string sItemCode = oRow["ItemCode"].ToString();
                    string sItemName = oRow["ItemName"].ToString();
                    string sUoM = oRow["UoM"].ToString();
                    string sUnitCost = oRow["UnitCost"].ToString();
                    string sLotNumber = oRow["LotNumber"].ToString();
                    double dblQty = Convert.ToDouble(oRow["Qty"]);
                    double dblRcvdQty = Convert.ToDouble(oRow["RcvdQty"]);
                    string sExpiryDate = oRow["ExpiryDate"].ToString();
                    string sMFRDate = oRow["MfrDate"].ToString();

                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO WT1 (DocNum, ItemCode, ItemName, UoM, LotNumber, Qty, RcvdQty, UnitCost, MfrDate, ExpiryDate) VALUES " +
                                            "(@DocNum, @ItemCode, @ItemName, @UoM, @LotNumber, @Qty, @RcvdQty, @UnitCost, @MfrDate, @ExpiryDate)";

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", sItemCode));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", sItemName));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", sUoM));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", sUnitCost));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", sLotNumber));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", dblQty));
                    oCommand.Parameters.Add(new SqlParameter("@RcvdQty", dblRcvdQty));
                    oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", sExpiryDate));
                    oCommand.Parameters.Add(new SqlParameter("@MfrDate", sMFRDate));

                    oCommand.ExecuteNonQuery();

                }
                return null;
            }catch(SqlException ex)
            {
                return ex;
            }
        }

        public SqlException UpdateTransfer(WTUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OWT SET PostingDate=@PostingDate, Reference=@Reference, SourceWhCode=@SourceWhCode, DestWhCode=@DestWhCode, Remarks=@Remarks, Driver=@Driver, Helper=@Helper, DocStatus=@DocStatus, DateCreated=@DateCreated, CreatedBy=@CreatedBy, DateModified=@DateModified, ModifiedBy=@ModifiedBy WHERE DOCNUM=@DocNum";


                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@SourceWhCode", oUnit.SourceWarehouse));
                oCommand.Parameters.Add(new SqlParameter("@DestWhCode", oUnit.DestinationWarehouse));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Driver", oUnit.Driver));
                oCommand.Parameters.Add(new SqlParameter("@Helper", oUnit.Helper));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                #region Removing the Current
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM WT1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));

                oCommand.ExecuteNonQuery();

                #endregion


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        string sItemCode = oRow["ItemCode"].ToString();
                        string sItemName = oRow["ItemName"].ToString();
                        string sUoM = oRow["UoM"].ToString();
                        string sUnitCost = oRow["UnitCost"].ToString();
                        string sLotNumber = oRow["LotNumber"].ToString();
                        double dblQty = Convert.ToDouble(oRow["Qty"]);
                        double dblRcvdQty = Convert.ToDouble(oRow["RcvdQty"]);
                        string sExpiryDate = oRow["ExpiryDate"].ToString();
                        string sMFRDate = oRow["MfrDate"].ToString();

                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = "INSERT INTO WT1 (DocNum, ItemCode, ItemName, UoM, LotNumber, Qty, RcvdQty, UnitCost, MfrDate, ExpiryDate) VALUES " +
                                                "(@DocNum, @ItemCode, @ItemName, @UoM, @LotNumber, @Qty, @RcvdQty, @UnitCost, @MfrDate, @ExpiryDate)";

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", sItemCode));
                        oCommand.Parameters.Add(new SqlParameter("@ItemName", sItemName));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", sUoM));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", sUnitCost));
                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", sLotNumber));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", dblQty));
                        oCommand.Parameters.Add(new SqlParameter("@RcvdQty", dblRcvdQty));
                        oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", sExpiryDate));
                        oCommand.Parameters.Add(new SqlParameter("@MfrDate", sMFRDate));

                        oCommand.ExecuteNonQuery();
                    }
                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }
        #endregion

        #region Browse Data
        public DataTable BrowseTransfer(string SourceWH, string sDocNumber, string sDocStatus)
        {
            SqlCommand oCommand = new SqlCommand();
            sDocNumber = '%' + sDocNumber + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_WTS WHERE DocNum LIKE @DocNumber AND SourceWHCode=@WHCode AND DocStatus=@DocStatus";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", sDocNumber));
            oCommand.Parameters.Add(new SqlParameter("@WHCode", SourceWH));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", sDocStatus));
            
            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable BrowseTransferByDocNum(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            sDocNumber = '%' + sDocNumber + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_WTS WHERE DocNum LIKE @DocNumber";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", sDocNumber));

            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }


        public DataTable BrowseTransferByReference(string sReference)
        {
            SqlCommand oCommand = new SqlCommand();
            sReference = '%' + sReference + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_WTS WHERE Reference LIKE @Reference";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", sReference));

            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable BrowseTransferBySourceWH(string sSourceWarehouse)
        {
            SqlCommand oCommand = new SqlCommand();
            sSourceWarehouse = '%' + sSourceWarehouse + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_WTS WHERE SourceWarehouse LIKE @SourceWhCode";
            oCommand.Parameters.Add(new SqlParameter("@SourceWhCode", sSourceWarehouse));

            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable BrowseTransferByDestWH(string sDestWH)
        {
            SqlCommand oCommand = new SqlCommand();
            sDestWH = '%' + sDestWH + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_WTS WHERE DestinationWarehouse LIKE @DestWhCode";
            oCommand.Parameters.Add(new SqlParameter("@DestWhCode", sDestWH));

            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataRow GetWarehouseTransfer(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OWT WHERE DocNum=@DocNumber";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", sDocNumber));

            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);
            DataRow retRow;
            retRow = null;
            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                retRow = oRow;
            }
            return retRow;

        }

        public DataTable GetLineItems(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM WT1 WHERE DocNum=@DocNumber";
            oCommand.Parameters.Add(new SqlParameter("@DocNumber", sDocNumber));

            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }
        #endregion

        #region Updating Data

        public void ChangeToTransitMode(string _WAREHOUSE, string _DOCNUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OWT SET DOCSTATUS='In-Transit' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.ExecuteNonQuery();


            string selectSQL = "SELECT * FROM WT1 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);


            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString(), oRow["UnitCost"].ToString());
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString(), oRow["UnitCost"].ToString(), "N/A", "Stock Transfer");
                }
            }


        }

        public void ChangeToReceived(string _WAREHOUSE, string _DOCNUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OWT SET DOCSTATUS='Received' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.ExecuteNonQuery();

            string selectSQL = "SELECT * FROM WT1 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);


            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    double _RcvdQty = Convert.ToDouble(oRow["RcvdQty"]);

                    if (_RcvdQty >= 1)
                    {
                        oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["RcvdQty"].ToString(), oRow["UnitCost"].ToString());
                    }
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    double _RcvdQty = Convert.ToDouble(oRow["RcvdQty"]);

                    if (_RcvdQty >= 1)
                    {
                        oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), oRow["RcvdQty"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString(), oRow["UnitCost"].ToString(), "N/A", "Stock Transfer");
                    }
                }
            }

        }

        public void CancelDocument(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OWT SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Retrieving Report
        public DataTable GetWarehouseStatus(string WhCode, DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_WTS_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@WHCODE", WhCode));
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetTransferForReceiving(string DestinationWarehouse)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DOCNUM, POSTINGDATE, SOURCEWHCODE, (SELECT WAREHOUSE.DSCRIPTION FROM WAREHOUSE WHERE WAREHOUSE.CODE=SOURCEWHCODE) AS SOURCEWHNAME, DATECREATED, CREATEDBY FROM OWT WHERE OWT.DESTWHCODE=@DESTWHCODE AND DocStatus='In-Transit'";
            
            oCommand.Parameters.Add(new SqlParameter("@DESTWHCODE", DestinationWarehouse));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        #endregion
        

    }
}
